package jssia.database.verification;

import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jssia.config.yaml.ConfigurationFactory;
import jssia.config.yaml.DatabaseConfiguration;
import jssia.config.yaml.YamlLoaders;
import jssia.database.ColumnInfo;
import jssia.database.DatabasesEnum;
import jssia.database.RDBMS;
import jssia.database.TableInfo;
import jssia.database.mysql.MysqlTypeMap;
import jssia.database.mysql.columns.CharColumn;
import jssia.database.mysql.columns.Column;
import jssia.database.mysql.columns.DecimalColumn;
import jssia.database.mysql.columns.EnumColumn;
import jssia.database.mysql.columns.FlagColumn;
import jssia.database.mysql.columns.SetColumn;
import jssia.database.mysql.constants.IndexStructure;
import jssia.database.mysql.constants.IndexType;
import jssia.database.mysql.databases.information_schema.Columns;
import jssia.database.mysql.databases.information_schema.Statistics;
import jssia.database.mysql.databases.information_schema.Tables;
import jssia.database.mysql.preparedstatements.DbInfoStatements;
import jssia.database.mysql.transactions.VoidTransaction;

import com.smoed.common.logging.Log;
import com.smoed.common.utilities.FrameworkUtils;
import com.smoed.common.utilities.StringUtils;

public class Jssia {

    private static Boolean isInitialized = new Boolean(false);

    public synchronized static void init() {
        synchronized(isInitialized) {
            if (!isInitialized) {
                String basePackage = ConfigurationFactory.getJssiaConfiguration().basePackage;
                final Class<? extends Enum> usersDatabasesClass = getUsersDatabasesClass(basePackage);
                initializeUsersDatabases(usersDatabasesClass);
                initializeTableColumnNames(usersDatabasesClass.getPackage().getName()); // all this does is set up toString() values for columns.
                isInitialized = true;
            }
        }
    }

    public synchronized static void verifySchema() {
        init();

        String basePackage = ConfigurationFactory.getJssiaConfiguration().basePackage;
        final Class<? extends Enum> usersDatabasesClass = getUsersDatabasesClass(basePackage);
        final Package basePkg = usersDatabasesClass.getPackage();
        final Map<Enum, List<Class<?>>> databaseToTablesMap = createDatabaseToTablesMap(basePkg.getName(), usersDatabasesClass);

        for (Enum e : usersDatabasesClass.getEnumConstants()) {
            DatabasesEnum db = (DatabasesEnum) e;

            Log.info("Verifying schema for database " + db);
            DatabaseCreator.verifyDatabaseSchema(db);
            verifyTables(db, basePkg, databaseToTablesMap.get(db));
            Log.info("Successfully verified the schema for database " + db);
        }
    }

    private static void initializeUsersDatabases(Class<? extends Enum> usersDatabasesClass) {
        EnumSet<? extends Enum> usersDatabases = EnumSet.allOf(usersDatabasesClass);
        for (DatabaseConfiguration dbConfig : ConfigurationFactory.getDatabaseConfigs()) {
            try {
                final Enum db = Enum.valueOf(usersDatabasesClass, dbConfig.src_db_name.toUpperCase());
                usersDatabases.remove(db);
                final String name = dbConfig.server_db_name;
                final String host = dbConfig.host;
                final String user = dbConfig.user;
                final String pass = dbConfig.pass;

                Field nameField = usersDatabasesClass.getDeclaredField("name");
                nameField.setAccessible(true);
                nameField.set(db, name);

                Field userField = usersDatabasesClass.getDeclaredField("user");
                userField.setAccessible(true);
                userField.set(db, user);

                Field passField = usersDatabasesClass.getDeclaredField("pass");
                passField.setAccessible(true);
                passField.set(db, pass);

                RDBMS rdbms = ((DatabasesEnum)db).getRdbms();
                rdbms.createDriverInstance();

                // ex: "jdbc:mysql://127.0.0.1:3306"
                final String connUrl = new StringBuilder("jdbc:").append(rdbms).append("://").append(host).append("/").toString();
                Field connUrlField = usersDatabasesClass.getDeclaredField("connectionUrl");
                connUrlField.setAccessible(true);
                connUrlField.set(db, connUrl);
            } catch(IllegalArgumentException e) {
                throw new IllegalArgumentException("The database " + dbConfig.src_db_name
                        + " does not exist in " + usersDatabasesClass, e);
            } catch (NoSuchFieldException e) {
                throw new IllegalStateException("Cannot not find a field inside of " + usersDatabasesClass
                        + ". Make sure the it contains string fields with the following names: 'name', 'user', 'pass', 'connectionUrl'.", e);
            } catch (IllegalAccessException e) {
                throw new IllegalStateException("Cannot not set a field inside of " + usersDatabasesClass, e);
            }
        }

        if (usersDatabases.size() > 0) {
            throw new IllegalStateException("You do not have a configuration defined for the following databases inside of "
                    + usersDatabasesClass + ": " + usersDatabases + ".  Please add a configuration to "
                    + YamlLoaders.DATABASE.getFilePath() + ".");
        }
    }

    private static Class<? extends Enum> getUsersDatabasesClass(String basePkg) {
        List<Class<DatabasesEnum>> databasesClasses = FrameworkUtils.getImplementingClasses(basePkg, DatabasesEnum.class);
        if (databasesClasses.size() > 1)
            throw new IllegalStateException("You can not implement multiple instances of " + DatabasesEnum.class
                    + ".  It is meant to be a singleton that lists all of the databases you want to use in your project.");
        else if (databasesClasses.size() == 0)
            throw new IllegalStateException("You must implement the interface " + DatabasesEnum.class + " somewhere in you code if you want to use jssia.");

        return (Class<? extends Enum>) databasesClasses.remove(0);
    }

    private static Map<Enum, List<Class<?>>> createDatabaseToTablesMap(String basePkg, Class<? extends Enum> usersDatabasesClass) {
        Map<Enum, List<Class<?>>> databaseToTablesMap = new HashMap<Enum, List<Class<?>>>();

        for (Class<? extends TableInfo> tableClass : FrameworkUtils.getClassesForAnnotation(basePkg, TableInfo.class)){
            String[] packages = tableClass.getPackage().getName().split("\\.");
            String dbName = packages[packages.length-1];
            try {
                final Enum db = Enum.valueOf(usersDatabasesClass, dbName.toUpperCase());

                List<Class<?>> tables = databaseToTablesMap.get(db);
                if (tables == null) {
                    List<Class<?>> tmp = new ArrayList<Class<?>>();
                    tmp.add(tableClass);
                    databaseToTablesMap.put(db, tmp);
                } else {
                    tables.add(tableClass);
                }
            } catch (IllegalArgumentException e) {
                throw new IllegalStateException("Could not find a database defined in "
                        + usersDatabasesClass + " with the name " + dbName + " To fix this "
                        + "issue either rename the directory containing tables name " + dbName
                        + " to a name matching one of the databases defined in " + usersDatabasesClass
                        + ", or add/rename a database within " + usersDatabasesClass + " to "
                        + "match the directory name.");
            }
        }

        return databaseToTablesMap;
    }
    private static void initializeTableColumnNames(String baseUserPkg) {
        String basePkg = Jssia.class.getPackage().getName().split("\\.")[0];

        final List<Class<TableInfo>> tables = FrameworkUtils.getClassesForAnnotation(baseUserPkg, TableInfo.class);
        tables.addAll(FrameworkUtils.getClassesForAnnotation(basePkg, TableInfo.class));

        for (Class<TableInfo> tableClass : tables){
            final String tableName = StringUtils.toDatabaseNamingConvention(tableClass.getSimpleName()) + ".";
            for (Field field : tableClass.getDeclaredFields()) {
                try {
                    final Object object = field.get(null);
                    if (object instanceof Column) {
                        final Column column = (Column) object;

                        String colName = field.getName().toLowerCase();
                        Field nameField = Column.class.getDeclaredField("name");
                        nameField.setAccessible(true);
                        nameField.set(column, colName);

                        Field fullyQualifiedNameField = Column.class.getDeclaredField("fullyQualifiedName");
                        fullyQualifiedNameField.setAccessible(true);
                        fullyQualifiedNameField.set(column, tableName + colName);
                    }
                } catch (IllegalAccessException e) {
                    throw new IllegalStateException("Cannot not set a field inside of " + Column.class, e);
                } catch (NoSuchFieldException e) {
                    throw new IllegalStateException("Cannot not find a field inside of " + Column.class
                            + ". Something must be corrupting the class.", e);
                }
            }
        }
    }

    private static void verifyTables(DatabasesEnum db, Package basePkg, List<Class<?>> tables) {
        final MysqlTypeMap mysqlDataTypeToColumnClassMap = new MysqlTypeMap();
        Map<String, TableSchema> serverTables = loadDatabaseTableSchemas(db, mysqlDataTypeToColumnClassMap);

        if (tables != null) {
            for (Class<?> tableClass : tables) {
                final TableInfo tableInfo = tableClass.getAnnotation(TableInfo.class);
                final String tableName = tableClass.getSimpleName().toLowerCase();
                final TableSchema tableSchema = serverTables.remove(tableName);

                if (tableSchema == null) {
                    DatabaseCreator.createTable(db, db.getName(), tableClass, tableInfo, mysqlDataTypeToColumnClassMap);
                } else {
                    SchemaVerificationUtils.verifyTableSchema(tableInfo, tableSchema);

                    for (Field field : tableClass.getDeclaredFields()) {
                        try {
                            final Object object = field.get(null);
                            if (object instanceof Column) {
                                final Column column = (Column) object;
                                final ColumnInfo columnInfo = field.getAnnotation(ColumnInfo.class);
                                final ColumnSchema colSchema = tableSchema.removeColumn(column.getSimpleName());
                                SchemaVerificationUtils.verifyColumnSchema(column, columnInfo, colSchema);
                            }
                        } catch (IllegalAccessException e) {
                            throw new IllegalStateException("Could not access " + field + " inside of " + tableClass, e);
                        }
                    }

                    if (tableSchema.numColumns() > 0)
                        throw new IllegalStateException("The source code for table '" + tableClass
                                + "' does not define the following columns: \n"+ tableSchema.getAllColumns());
                }
            }
        }

        TableSourceGenerator.generateTableSource(basePkg, db.toString().toLowerCase(), serverTables.values());
    }

    private static Map<String, TableSchema> loadDatabaseTableSchemas(DatabasesEnum db, final MysqlTypeMap mysqlDataTypeToColumnClassMap) {
        final Connection conn = db.getConnection();
        final String dbName = db.getName();

        final Map<String, TableSchema> tables = new HashMap<String, TableSchema>();

        final Pattern singleQuotedStringPattern = Pattern.compile("'\\w+?'");

        db.executeTransaction(new VoidTransaction() {
            @Override
            public void execute() throws SQLException {
                ResultSet tablesResultSet = null;
                ResultSet columnsResultSet = null;
                ResultSet indexColumnsResultSet = null;
                try {
                    tablesResultSet = DbInfoStatements.selectTablesForDatabase(conn, dbName);
                    while (tablesResultSet.next()) {
                        final String tableName = Tables.TABLE_NAME.get(tablesResultSet);
                        final TableSchema table = new TableSchema(
                                tableName,
                                Tables.ENGINE.get(tablesResultSet),
                                Tables.TABLE_COLLATION.get(tablesResultSet));

                        tables.put(tableName.replaceAll("_", "").toLowerCase(), table);

                        columnsResultSet = DbInfoStatements.selectColumnsForTable(conn, dbName, tableName);
                        while (columnsResultSet.next()) {

                            String columnName = Columns.COLUMN_NAME.get(columnsResultSet);
                            String columnType = Columns.COLUMN_TYPE.get(columnsResultSet);

                            StringBuilder columnClassName = new StringBuilder();

                            columnClassName.append(Columns.DATA_TYPE.get(columnsResultSet));
                            if (columnType.contains("unsigned"))
                                columnClassName.append(" unsigned");

                            BigInteger length = Columns.CHARACTER_MAXIMUM_LENGTH.get(columnsResultSet);

                            Class<? extends Column> type = mysqlDataTypeToColumnClassMap.getColumnClass(columnClassName.toString());
                            if (type == null)
                                throw new IllegalStateException("Sorry we currently do not support the " + columnClassName + " column type.");

                            Set<String> values = null;
                            if (type == EnumColumn.class || type == SetColumn.class) {
                                Matcher matcher = singleQuotedStringPattern.matcher(columnType);
                                values = new HashSet<String>();
                                while (matcher.find()) {
                                    values.add(matcher.group().replaceAll("'", "").toUpperCase());
                                }
                            } else if (length != null && length.longValue() == 0 && type == CharColumn.class) {
                                type = FlagColumn.class;
                            }


                            IndexSchema index = null;
                            String indexName = Statistics.INDEX_NAME.get(columnsResultSet);
                            if (indexName != null) {

                                indexName = indexName.toLowerCase();
                                IndexType indexType = IndexType.INDEX;
                                long nonUnique = Statistics.NON_UNIQUE.get(columnsResultSet);

                                if (indexName.equalsIgnoreCase("PRIMARY")) {
                                    indexType = IndexType.PRIMARY;
                                } else if (nonUnique == 0) {
                                    indexType = IndexType.UNIQUE;
                                }

                                IndexStructure indexStructure = IndexStructure.valueOf(Statistics.INDEX_TYPE.get(columnsResultSet));

                                List<String> indexColumns = new ArrayList<String>();
                                indexColumns.add(columnName);

                                indexColumnsResultSet = DbInfoStatements.selectColumnsForIndex(conn, dbName, tableName, indexName);
                                while (indexColumnsResultSet.next()) {
                                    indexColumns.add(Statistics.COLUMN_NAME.get(indexColumnsResultSet));
                                }
                                indexColumnsResultSet.close();

                                index = new IndexSchema(indexName, indexType, indexStructure, indexColumns.toArray(new String[indexColumns.size()]));
                            }

                            final String columnDefault = Columns.COLUMN_DEFAULT.get(columnsResultSet);

                            final ColumnSchema col = new ColumnSchema(
                                    columnName,
                                    type,
                                    length,
                                    Columns.COLLATION_NAME.get(columnsResultSet),
                                    Columns.IS_NULLABLE.get(columnsResultSet).equalsIgnoreCase("yes"),
                                    Columns.EXTRA.get(columnsResultSet).equalsIgnoreCase("auto_increment"),
                                    columnDefault == null ? "" : columnDefault,
                                    index,
                                    values);

                            if (type == DecimalColumn.class) {
                                int precision = Columns.NUMERIC_PRECISION.get(columnsResultSet).intValue();
                                int scale = Columns.NUMERIC_SCALE.get(columnsResultSet).intValue();
                                col.setPrecisionAndScale(precision, scale);
                            }

                            table.addColumn(col);
                        }
                        columnsResultSet.close();
                    }
                } finally {
                    if (tablesResultSet != null) tablesResultSet.close();
                    if (columnsResultSet != null) columnsResultSet.close();
                    if (indexColumnsResultSet != null) indexColumnsResultSet.close();
                }
            }
        });

        return tables;
    }
}
